Parent | Child | Previous | Next
 

Creating a Table Lookup

Description

You can use a record in another table as the source of the lookup values. You can also define a lookup that fills in several fields with values from fields you select from the lookup table. For example, you might define a lookup for the Prod_ID field. The lookup finds the matching Prod_ID record in the Product table and fills in the Price field.

Defining a table lookup involves:

  • Choosing the lookup table.

  • Specifying a linking field from the lookup table.

  • Specifying a display order for lookup table records (optional).

  • Including a filter to display only selected lookup table records (optional).

  • Mapping the fields from the lookup to the current table.

When you use a table lookup, data is copied from the lookup table to the current (target) table.

  1. If you selected "Table Lookup", select the table that is providing the information from the Table field. Click the 'open folder' icon to navigate to and select the table. Typically, the lookup table has a field in common with the fill (destination) table.

    If you have defined an alias for the location of the table, you can substitute the alias for the path of the table.

  2. In the Linking Field enter the field name or an expression that evaluates to a field name in the lookup table. Choose the field that has equal values (but not necessarily the same field names) in both lookup and fill tables. For example, in a Products table with a lookup to a Vendors table, the Field Rule is defined for the Product table's Vend_ID field. The linking field from the Vendor table is also Vend_ID. Click the 'x-y' icon to use the Expression Builder to help construct an expression.

  3. Optionally, enter the a field name or an expression in the Display Order field that will order the list that you retrieve from the lookup table. Click the 'x-y' icon to use the Expression Builder to help construct an expression.

  4. Optionally, in the Filter field enter an expression that will select records for the list that you retrieve from the lookup table. Click the 'x-y' icon to use the Expression Builder to help construct an expression.

    • Mapping the Fields From the Lookup to the Current Table

      For each field you want to fill and/or display in the lookup window, you need to complete one row in the embedded browse. Fields are mapped from the lookup (source) table to the current (fill) table. Fields that you match must be of the same data type. A field mapping specifies:

      • The name of the source field.

      • The name of the field to be filled in (optional).

      • Whether or not to display the source field in the lookup window.

      • The display width of the source field in the lookup window.

      • A condition that determines whether the field provides a value (optional).

      • If you are working with a set, you can fill fields in only the table for which the rule is defined.

      • Any row in the embedded browse can have a condition associated with it. For more information, see Conditional Table Lookup. You can display multiple fields from the lookup table. You do not have to display the field from the lookup table that you insert into the current table.

  5. In the Source column of the embedded browse, click the 'down' icon to select the fields from the lookup (source) table that you want to use. Place each field in a separate row.

  6. In the Fill column, to the right of each Source entry, click the 'down' icon to select the fields from the current (fill) table that you want to fill.

  7. In the Display column, to the right of each Fill entry, select "Yes" or "No" to indicate whether this field will be visible in the user's selection list.

  8. Optionally, in the Width column specify the width (in characters) that each displayed field will have in the user's selection list. If you do not enter a setting, the entire field is displayed.

  9. Optionally, use the Condition column in the embedded browse to specify that a record may not be included in the selection list. It will only appear if the expression you write evaluates to TRUE (.T.). Refer to Displaying Selected Lookup Table Records (Filtered Lookups).

  10. Optionally, check User can add records if you would like the user to be able to add more entries to the lookup table.

  11. Optionally, check User can edit records if you would like the user to be able to modify entries to the lookup table.

  12. Optionally, check Unique records only if you want to avoid duplicate entries in the lookup list.

  13. If you checked User can add records, make a selection from Form layout to edit records.

  14. Optionally, change the selection in Allow Exceptions. The options are:

    • "Allow any value"

    • "Force closest match"

    • "Popup on mismatch"

    • "Allow any/fill only on popup"

  15. Optionally, un-check Display as Drop-down list box if you would like the choices to appear instead in a pop-up dialog box. Refer to Comparing Drop-down Lists and Popup Dialogs.

    • 1. Optionally, if you chose to use a popup dialog, make a selection from Auto popup. Specify whether the Lookup window displays on demand or automatically. The options are:

      • "Do not automatically popup" - The Lookup window displays on demand. You must click the Pop-up smart button attached to the Lookup field. This is the default.

      • "Popup on entrance" - The Lookup window displays every time the cursor enters the field.

      • "Popup conditionally on entrance" - Build a conditional expression that determines whether the window displays automatically. For example, the Pop-up condition: ISBLANK("CUST_ID") causes the Customer Lookup window to appear only if the Cust_ID field is empty.

    • 2. If you selected "Popup conditionally on entrance" in step 9b, enter an expression that defines the Popup condition. The expression must resolve to either TRUE (.T.) or FALSE (.F.). Click to display the Expression Builder to help you define the expression.

    • 3. Optionally, select a value from the Position list to indicate where the Lookup window should appear. The listed positions are relative to the Alpha Anywhere Main Application window. The options are:

      • "Cascading" - displays the window just below the form window title, so that both title bars are visible.

      • "Top left"

      • "Top center"

      • "Top right"

      • "Middle left"

      • "Middle center"

      • "Middle right"

      • "Bottom left"

      • "Bottom center"

      • "Bottom right"

      • "Custom" - lets you specify the position of the Lookup window in inches or centimeters. Indicate the distance from the upper-left corner of the layout window both horizontally (X) and vertically (Y).

      • "Sticky" - the popup remembers and reappears at its last position

    • 4. If you selected "Custom" in step 9d, enter position values in the Custom X and Custom Y fields. Enter dimension values in the Custom Width and Custom Height fields.

    • 5. Clear Allow maximized display if you want to prevent the popup dialog from being maximized.